using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;

/// <summary>
/// Summary description for SqlUtils
/// </summary>
public static class SqlUtils
{
    public delegate T ElementConverter<T>(string input);

    public static string GetConnectionString()
    {
        return System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
    }

    public static void ExecuteStoredProcedure(System.Web.HttpContext context, string procedure, Dictionary<String, Object> parameters)
    {
        // Get the current user
        string user = context.User.Identity.Name;
        parameters.Add("@user", user);

        string connectionString = GetConnectionString();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(procedure, connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            foreach (string key in parameters.Keys)
            {
                command.Parameters.AddWithValue(key, parameters[key]);
            }

            command.ExecuteNonQuery();
        }
    }

    public static void ExecuteStoredProcedure<T>(System.Web.HttpContext context, string procedure, Dictionary<String, Object> parameters, List<T> results)
        where T : IPsSpecDataReader, new()
    {
        // Get the current user
        string user = context.User.Identity.Name;
        parameters.Add("@user", user);

        string connectionString = GetConnectionString();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(procedure, connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            foreach (string key in parameters.Keys)
            {
                command.Parameters.AddWithValue(key, parameters[key]);
            }

            SqlDataReader reader = command.ExecuteReader();
            System.Collections.Generic.Dictionary<string, Int32> columnMappings =
                InitializeColumnMappings(reader);

            while (reader.Read())
            {
                T result = new T();
                result.Initialize(reader, columnMappings);
                results.Add(result);
            }
            reader.Close();
            connection.Close();
        }
    }

    public static T[] CreateArray<T>(string input, ElementConverter<T> converter)
    {
        if (input == null)
            return null;

        return CreateArray<T>(input, converter, ",");
    }

    public static T[] CreateArray<T>(string input, ElementConverter<T> converter, string splitter)
    {
        if (input == null)
            return null;

        string[] items = System.Text.RegularExpressions.Regex.Split(input, splitter);
        List<T> results = new List<T>();

        for (int counter = 0; counter < items.Length; counter++)
        {
            if(! String.IsNullOrEmpty(items[counter]))
            {
                results.Add(converter(items[counter].Trim()));
            }
        }

        return results.ToArray();
    }

    public static Dictionary<string, int> InitializeColumnMappings(SqlDataReader reader)
    {
        Dictionary<string, Int32> columnMappings = new Dictionary<string, int>();

        //Retrieve column schema into a DataTable.
        DataTable schemaTable = reader.GetSchemaTable();

        //For each field in the table...
        foreach (DataRow field in schemaTable.Rows)
        {
            // Map the first item (which is the column name) to its
            // column
            columnMappings[field.ItemArray[0].ToString()] = (int)field.ItemArray[1];
        }

        return columnMappings;
    }

}
